package com.presalept.manipulatedata;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.presalept.polodo.EventDO;

import net.sf.json.*;

public class RetrieveEventdata {
	private String dbOneEventJson;
	private String dbAllEventofProjectJson;

	private List<EventDO> EventofProjectList;
	
	//get all events of inputting projectid;
	
	public String GetEventlist(int ProjectId)throws SQLException{
		Connection dbConn = null;
		PreparedStatement dbPreStmt = null;
		ResultSet dbRs = null;
		String dbSql = "select * from event where projectid = ?";
		
		try{
			//建立数据库连接，获取特定用户的项目集合；
			dbConn = DbManager.getConnection();
			
			dbPreStmt = dbConn.prepareStatement(dbSql);
			
			dbPreStmt.setInt(1, ProjectId);
			
			dbRs = dbPreStmt.executeQuery();	
			
			EventofProjectList = CreateEventlist(dbRs);
			
			dbAllEventofProjectJson = CreateEventlistJson(EventofProjectList);			
			
		}finally{
			if (dbRs != null) dbRs.close();
			if (dbConn != null) dbConn.close();
			
		}
		return dbAllEventofProjectJson;
		
	}
	
	//get one event of inputting eventid
	public String GetOneEvent(int EventId) throws SQLException{
		Connection dbConn = null;
		PreparedStatement dbPreStmt = null;
		ResultSet dbRs = null;
		String dbSql = "select * from event where eventid = ?";
		
		try{
			//建立数据库连接，获取特定用户的项目集合；
			dbConn = DbManager.getConnection();
			
			dbPreStmt = dbConn.prepareStatement(dbSql);
			
			dbPreStmt.setInt(1, EventId);
			
			dbRs = dbPreStmt.executeQuery();	
			
			dbOneEventJson = CreateOneEventJson(CreateOneEvent(dbRs));			
			
		}finally{
			if (dbRs != null) dbRs.close();
			if (dbConn != null) dbConn.close();
			
		}
		return dbOneEventJson;	
		
	}
	
	
	//get all events of inputting projectid

	private List<EventDO> CreateEventlist(ResultSet dbRs) throws SQLException{
		
		List<EventDO> eventlist = new ArrayList<EventDO>();
		
		while(dbRs.next()){
			EventDO eventdo = new EventDO();
			eventdo.setEventId(dbRs.getInt("eventid"));
			eventdo.setEventName(dbRs.getString("eventname"));
			eventdo.setProjectIdofEvent(dbRs.getInt("projectidofevent"));
			eventdo.setEventDescription(dbRs.getString("eventdescription"));
			eventdo.setVoicerecordurl(dbRs.getString("voicerecordurl"));
			eventdo.setCreaterid(dbRs.getInt("createrid"));
			eventdo.setCreateDate(dbRs.getString("createdate"));
			eventdo.setUpdatestatus(dbRs.getInt("updatestatus"));
			eventdo.setFeedbackid(dbRs.getInt("feedbackid"));
			eventlist.add(eventdo);	
		}		
		return eventlist;
	}
	
	//get all events of inputting projectid
	
	private String CreateEventlistJson(List<EventDO> eventlist){
		
		/*dbJasonAll = listPmpdAll.get(0).getUserName() + "  " +
				listPmpdAll.get(0).getProjectName() + "  " +
			listPmpdAll.get(0).getlistEvent().get(0).getCreateDate().toString();*/
		JSONArray jsonObject = JSONArray.fromObject(eventlist);
		return jsonObject.toString();	
	}
	
	//get one event of inputting eventid
	
	private EventDO CreateOneEvent(ResultSet dbRs) throws SQLException{
		
		EventDO eventdo = new EventDO();
		dbRs.next();
		eventdo.setEventId(dbRs.getInt("eventid"));
		eventdo.setEventName(dbRs.getString("eventname"));
		eventdo.setProjectIdofEvent(dbRs.getInt("projectidofevent"));
		eventdo.setEventDescription(dbRs.getString("eventdescription"));
		eventdo.setVoicerecordurl(dbRs.getString("voicerecordurl"));
		eventdo.setCreaterid(dbRs.getInt("createrid"));
		eventdo.setCreateDate(dbRs.getString("createdate"));
		eventdo.setUpdatestatus(dbRs.getInt("updatestatus"));
		eventdo.setFeedbackid(dbRs.getInt("feedbackid"));	
		return eventdo;
	}
	
	//get one event json
	
	private String CreateOneEventJson(EventDO eventdo){
		JSONObject jsonObject = JSONObject.fromObject(eventdo);
		return jsonObject.toString();
	}

}
